/* $Id: DbHelper.java 89 2008-01-22 02:38:52Z osborn.steven $
 * 
 * Undroid Package Manager
 *
 * Copyright 2007 Steven Osborn
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>. 
 */
package com.bitsetters.undroid.helpers;

import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.bitsetters.android.common.BulletedText;
import com.bitsetters.undroid.R;

import com.bitsetters.undroid.AppEntry;


/**
 * DBHelper class.  
 * 
 * @author Steven Osborn - http://steven.bitsetters.com
 */
public class DbHelper {

    public static final String TAG = "UNDROID[DbHelper]";

    private SQLiteDatabase db;
    private Context mContext;

    private static final String DATABASE_NAME = "undroid";
    private static final int DATABASE_VERSION = 1;
    
    private static final String TABLE_APPS = "apps";
    private static final String TABLE_APP_CATEGORIES = "app_categories";
    private static final String TABLE_REPOS = "repos";

    private static final String APPS_CATEGORIES_CREATE =
        "CREATE TABLE app_categories ( " +
        "app_id integer not null, " +
        "category_id integer not null, " +
        "PRIMARY KEY (app_id,category_id));";
    
    private static final String APPS_CREATE =
        "CREATE TABLE apps ( " +
        "id integer primary key autoincrement, " +
        "repo_id integer not null, " +
        "label text not null, " +
        "package_name text not null, " +
        "version text not null, " +
        "release text not null, " +
        "location text not null, " +
        "size integer not null, " +
        "author text, website text, " +
        "summary text not null, " +
        "checksum text non null, " +
        "description text, " +
        "license text, " +
        "license_href text, " +
        "copyright text);";
        
    private static final String REPO_CREATE =
        "CREATE TABLE repos ( " +
        "id integer primary key autoincrement, " +
        "location text unique, " +
        "allow_delete integer default 1, " +
        "name text not null, " +
        "website text, " +
        "summary text not null, " +
        "checksum text, " +
        "timestamp text not null);";
    
    
    private static final String[] APP_FIELDS = 
        new String[] {"id","label","package_name","version","release",
        "location","size","author","website",
        "summary","checksum","description","license",
        "license_href","copyright"};


    /**
     * Answers the number of applications in a particular category
     *
     * @param catId
     * @return
     */
    public int getCategoryCount(int catId) {
        String where = "app_categories.category_id=" + String.valueOf(catId);
        
        // "All" category
        if(catId == 1) {
            where = "app_categories.category_id > 9";
        }
        
        SQLiteStatement st = db.compileStatement("SELECT count(*) FROM "
        		+ "apps left join app_categories on " 
                + "apps.id=app_categories.app_id where "
                + where );
        
        return (int) st.simpleQueryForLong();
    }

    /**
     * Answers the time a given repository was last updated 
     *
     * @param location
     * @return time stamp
     */
    public long getRepoTimestamp(String location) {
        Cursor c = db.query(TABLE_REPOS, new String[] {"timestamp"},
                "location=?",new String[] {location}, null, null, null);

        long timestamp = 0;
        if(c.next()) {
            String st = c.getString(0);
            if(st.length() > 0) {
                timestamp = Long.valueOf(st);
            }
        }
        return timestamp;
    }

    /**
     *  
     *
     * @return list of repositories
     */
    public List<BulletedText> getRepoList() {
        List<BulletedText> items = new ArrayList<BulletedText>();

        Cursor c = db.query("SELECT label,summary FROM " + TABLE_REPOS,null);

        c.first();
        for (int i = 0; i < c.count(); ++i) {
            BulletedText item = new BulletedText(c.getString(0),
                    mContext.getResources().getDrawable(R.drawable.internet16)
                    ,c.getString(1));
            items.add(item);
            c.next();
        }


        return items;
    }

    /**
     * Answers the last checksum for a given repository
     *
     * @param repoLocation
     * @return digest
     */
    public String getLastChecksum(String repoLocation) {
        Cursor c = db.query(TABLE_REPOS, new String[] {"checksum"},
                "location=?",new String[] {repoLocation}, null, null, null);

        String sum = new String();
        if(c.next()) {
            sum = c.getString(0);
        }
        return sum;
    }

    /**
     * Removes a given repository cache from the database
     *
     * @param location
     */
    public void resetRepo(String location) {
        String repoId  = String.valueOf(getRepoId(location));
        db.delete(TABLE_APPS, "repo_id=?", new String[] {repoId});
    }

    /**
     * Answers a repository id given it's location 
     *
     * @param location
     * @return repository id
     */
    public int getRepoId(String location) {
        Cursor c = db.query(TABLE_REPOS, new String[] {"id"},
                "location=?",new String[] {location}, null, null, null);

        int id = 0;
        if(c.next()) {
            id = c.getInt(0);
        }
        return id;
    }

    /**
     * Adds a repository to the database cache 
     *
     * @param location
     * @param name
     * @param website
     * @param summary
     * @param allowDelete
     */
    public void addRepo(String location, String name, String website,
            String summary, boolean allowDelete) {
        ContentValues values = new ContentValues();
        values.put("location", location);
        values.put("name", name);
        values.put("website", website);
        values.put("summary", summary);
        if(allowDelete) {
            values.put("allow_delete", 1);
        } else {
            values.put("allow_delete", 0);
        }
        values.put("timestamp", 0);
        db.insert(TABLE_REPOS, "", values);
    }

    /**
     * 
     * @param repoId
     */
    public void updateRepoTimeStamp(String repoLocation) {
        ContentValues values = new ContentValues();
        values.put("timestamp", getCurrentTimeStamp());
        db.update(TABLE_REPOS, values, "location=?",
                new String[] {String.valueOf(repoLocation)});
    }

    /**
     *  
     *
     * @return current time
     */
    public String getCurrentTimeStamp() {
        return String.valueOf(System.currentTimeMillis());
    }

    /**
     * 
     * @param repoId
     * @param checkSum
     */
    public void updateRepoCheckSum(String repoLocation, String checkSum) {
        ContentValues values = new ContentValues();
        values.put("checksum", checkSum);
        values.put("timestamp", getCurrentTimeStamp());
        db.update(TABLE_REPOS, values, "location=?",
                new String[] {String.valueOf(repoLocation)});
    }

    /**
     * 
     * @param catid
     * @return
     */
    public List<AppEntry> getCategoryPrograms(int catid) {
        List<AppEntry> apps = new ArrayList<AppEntry>();
               
        String where = "category_id=" + catid;
        
        // "All" category
        if(catid == 1) {
            where = "category_id > 9";
        }

        Cursor c = db.query("select * from apps left join app_categories on " + 
                     "apps.id = app_categories.app_id where " + where, null );

        c.first();
        AppEntry app = null;
        for (int i = 0; i < c.count(); ++i) {
            app = new AppEntry();

            app.appId = c.getInt(c.getColumnIndex("id"));
            app.label = c.getString(c.getColumnIndex("label"));
            app.package_name = c.getString(c.getColumnIndex("package_name"));
            app.version = c.getString(c.getColumnIndex("version"));
            app.release = c.getInt(c.getColumnIndex("release"));
            app.location = c.getString(c.getColumnIndex("location"));
            app.size = c.getInt(c.getColumnIndex("size"));
            app.author  = c.getString(c.getColumnIndex("author"));
            app.website = c.getString(c.getColumnIndex("website"));
            app.summary = c.getString(c.getColumnIndex("summary"));
            app.checksum = c.getString(c.getColumnIndex("checksum"));
            app.description = c.getString(c.getColumnIndex("description"));
            app.license = c.getString(c.getColumnIndex("license"));
            app.license_href = c.getString(c.getColumnIndex("license_href"));
            app.copyright = c.getString(c.getColumnIndex("copyright"));
            apps.add(app);
            c.next();
        }
        
        return apps;
    }
    

    /**
     * 
     * @param appId
     * @return
     */
    public AppEntry getApp(int appId) {
        AppEntry app = new AppEntry();
        Cursor c = db.query(false, TABLE_APPS, APP_FIELDS,
                "id="+appId,null, null, null, null);

        if(c.next()) {
            app.appId = c.getInt(c.getColumnIndex("id"));
            app.label = c.getString(c.getColumnIndex("label"));
            app.package_name = c.getString(c.getColumnIndex("package_name"));
            app.version = c.getString(c.getColumnIndex("version"));
            app.release = c.getInt(c.getColumnIndex("release"));
            app.location = c.getString(c.getColumnIndex("location"));
            app.size = c.getInt(c.getColumnIndex("size"));
            app.author  = c.getString(c.getColumnIndex("author"));
            app.website = c.getString(c.getColumnIndex("website"));
            app.summary = c.getString(c.getColumnIndex("summary"));
            app.checksum = c.getString(c.getColumnIndex("checksum"));
            app.description = c.getString(c.getColumnIndex("description"));
            app.license = c.getString(c.getColumnIndex("license"));
            app.license_href = c.getString(c.getColumnIndex("license_href"));
            app.copyright = c.getString(c.getColumnIndex("copyright"));
        }
        
        c = db.query(false, TABLE_APP_CATEGORIES, new String[] {"category_id"},
                "app_id="+appId, null, null, null, null);
        
        while(c.next()) {
            app.categories.add(c.getInt(0));
        }
        
        return app;
    }

    /**
     * 
     * @return
     */
    public int getAppID(String checksum) {
        Cursor c = db.query(false, TABLE_APPS, new String[] {"id"},
                "checksum=?",new String[] {checksum}, null, null, null);

        if(c.next()) {
            return c.getInt(0);
        }
        
        return 0;
    }
    
    /**
     * 
     * @param app
     */
    public void addApp(AppEntry app) {
        ContentValues values = new ContentValues();

        values.put("label", app.label);
        values.put("package_name",app.package_name);
        values.put("version", app.version);
        values.put("release",app.release);
        values.put("location", app.location);
        values.put("size", app.size);
        values.put("author", app.author);
        values.put("website", app.website);
        values.put("summary",app.summary);
        values.put("checksum", app.checksum);
        values.put("description", app.description);
        values.put("license", app.license);
        values.put("license_href", app.license_href);
        values.put("copyright", app.copyright);
        values.put("repo_id", app.repoId);

        db.insert(TABLE_APPS, null, values);
        
        app.appId = getAppID(app.checksum);
        
        for(int i=0; i < app.categories.size();i++) {
            values = new ContentValues();
            values.put("app_id",app.appId);
            values.put("category_id", app.categories.get(i));
            db.insert(TABLE_APP_CATEGORIES, null, values);
        }
    }

    /**
     * Close database connection
     */
    public void close() {
        db.close();
    }

    /**
     * 
     */
    private void init() {
        try {
            db = mContext.openDatabase(DATABASE_NAME, null);
        } catch (FileNotFoundException e) {
            try {
                db = mContext.createDatabase(DATABASE_NAME, DATABASE_VERSION, 0,
                        null);
                db.execSQL(APPS_CREATE);
                db.execSQL(APPS_CATEGORIES_CREATE);
                db.execSQL(REPO_CREATE);
                addRepo(RepoReader.UNDROID_REPO,
                        mContext.getResources().getString(R.string.undroid_repo_name),
                        mContext.getResources().getString(R.string.undroid_website),
                        mContext.getResources().getString(R.string.undroid_repo_summary),
                        false);
            } catch (FileNotFoundException e1) {
                db = null;
            }
        }
    }
    
    /**
     * Constructor
     */
    public DbHelper(Context ctx) {
        mContext = ctx;
        init();
    }

}
